Communication with MS Excel

Even if it is not mandatory that MS Excel is installed to run PetroVR, there are cases where the application assumes the presence of Excel version 12 (2007) or later, since several features communicate with it to input or to output data in a variety of ways.

PetroVR automatically opens an instance of Excel (if none is already open) every time it starts, establishing an OLE Automation communication where both applications can send requests and responses to one another dynamically. If the Excel instance was opened by PetroVR, it will close when you exit the application.

Excel will open first in invisible mode, and will only be displayed if invoked, for example by a command to export results or by linking a variable. You can also open an already linked sheet by clicking the Bring Excel on top button in the Excel Input Editor.

Copying values from/to Excel

The most basic kind of communication consists in simply copying and pasting single values or ranges from one application to the other using the standard Windows commands.

When copying values from MS Excel, only the display value of cells is copied to the clipboard. This means that if this value is rounded because of the limit in the number of decimals, the rounded value will be copied and pasted into PetroVR. This may even raise validation issues, e.g. when pasting composition tables which must add up to 100%. To make sure that a more accurate value is copied you should adjust the cell format in Excel before copying.

On the other hand, copying values from PetroVR to Excel uses the exact original values instead (not rounded).

PetroVR supports working with .xls, .xlsx, .xlsm and .xlsb Excel workbook formats.

Importing/exporting data to Excel

A more advanced means of importing/exporting data in bulk between the applications is provided by two specific tools:

Dynamic links to Excel

The import/export tools mentioned above are not directly connected to the simulation itself, but are used manually on specific occasions to facilitate the exchange of fixed sets of data between PetroVR and Excel. Other tools, instead, dynamically link the exchange of specific data during the simulation, and establish a direct and permanent connection between the PetroVR model and one or more Excel sheets. These are:

  • Excel Input: Link a single-value or an array input variable to a cell or range of cells in an Excel worksheet; see Input Modes.
  • Excel Links: Link a table variable to a range of cells in an Excel worksheet.
  • Excel Output: Write the value of an input variable to a cell or range of cells every time the simulation is run.
  • Excel Cases: Define and run a given number of cases from a single base project using one or more Excel Input variables modified by giving incremental values to an Excel cell.

In all cases where data are read from Excel worksheets, PetroVR refers not to cell locations (e.g. "A30" or "Sheet1!$A$1:$A$13") but to named ranges. You must first define named ranges in the source Excel spreadsheet to be able to read them from PetroVR.

Linked inputs are updated every time the interface is refreshed or an input is entered; also, when the PetroVR model is opened and at the beginning of each simulation run. If the Excel file is being edited at the same time as the PetroVR project, PetroVR will read the current version of the file and not the last saved one.

Excel does not allow two instances of the same file to be opened at the same time in write mode; therefore, when an Excel file is referenced by a dynamic link in the currently open PetroVR project, the file is locked and can only be opened from outside PetroVR in read-only mode. In such cases you can open the Windows Task Manager to find out whether more than one instance of Excel is open; if so, only one is connected with PetroVR.

There are two workarounds to avoid the read-only Excel message:

  1. If you are already working in PetroVR, instead of opening the file from outside the application, click on the Excel button in the Excel Input Editor.
  2. Instead of opening first PetroVR and then Excel, open first Excel and then PetroVR, because PetroVR always checks if Excel is already open and if so uses the existing instance. Only one process will be shown in the Task Manager and no read-write conflicts will arise.

When an Excel workbook is already open by another user or application, PetroVR can only open a read-only version, which will not be updated with any changes made to the main file until the latter is saved, and PetroVR closes and re-opens the workbook.